package com.fin.zw.intelligentbot.repository;

import com.fin.zw.intelligentbot.entity.TaskCustomer;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface TaskCustomerRepository extends JpaRepository<TaskCustomer,String>,QuerydslPredicateExecutor<TaskCustomer> {

    @Query(value = "select * from task_customer where company_id=:companyId and start_time>=:statTime and DATE_FORMAT(start_time,'%H:%i:%s') >=:start and " +
            "DATE_FORMAT(start_time,'%H:%i:%s') < :end", nativeQuery = true)
    List<TaskCustomer> getInfoByTime(@Param("statTime") String statTime,
                                     @Param("companyId") String companyId,
                                     @Param("start") String start,
                                     @Param("end") String end);

    @Query(value = "select * from task_customer where start_time>=:statTime and DATE_FORMAT(start_time,'%H:%i:%s') >=:start and " +
            "DATE_FORMAT(start_time,'%H:%i:%s') < :end", nativeQuery = true)
    List<TaskCustomer> getInfoByTime(@Param("statTime") String statTime,
                                     @Param("start") String start,
                                     @Param("end") String end);

    @Query(value = "select a.* from task_customer a,dial_task b where a.task_id=b.id and if(:userId is not null,b.create_id=:userId,1=1) " +
            "and if(:taskStatus is not null,b.status=:taskStatus,1=1) and if(:taskId is not null,a.task_id=:taskId,1=1) " +
            "and if(:taskName is not null,b.name=:taskName,1=1) and if(:sceneId is not null,b.sce_id=:sceneId,1=1) " +
            "and if(:phoneStatus is not null,a.status=:phoneStatus,a.status not in('NEW','READY')) and if(:phone is not null,a.phone=:phone,1=1) " +
            "and if(:customerName is not null,a.name=:customerName,1=1)", nativeQuery = true)
    List<TaskCustomer> getInfoByCondition(@Param("userId") String userId,
                                          @Param("taskStatus") String taskStatus,
                                          @Param("taskId") String taskId,
                                          @Param("taskName") String taskName,
                                          @Param("sceneId") String sceneId,
                                          @Param("phoneStatus") String phoneStatus,
                                          @Param("phone") String phone,
                                          @Param("customerName") String customerName);

    @Query(value = "select * from task_customer where task_id=:taskId and (attitude_id in(:attitudes) or status in('READY','NEW'))", nativeQuery = true)
    List<TaskCustomer> getInfoByTaskIdAndAttitudes(@Param("taskId") String taskId,
                                                   @Param("attitudes") List<String> attitudes);

    @Transactional
    @Modifying
    @Query(value = "delete from task_customer where task_id=:taskId", nativeQuery = true)
    int deleteByTaskId(@Param("taskId") String taskId);
}
